/*
EXEC sp_process_specification_file_setup_procedure 'assembly'
 */

ALTER PROCEDURE sp_process_specification_file_setup_procedure ( @processName VARCHAR(50) )
AS

BEGIN
	DECLARE @baseProduct_ProductID INT
	DECLARE @baseProductName VARCHAR(50)
	DECLARE @selected INT
	DECLARE @part_Number VARCHAR(50)
	DECLARE @processStepID INT
	DECLARE @specFileName VARCHAR(50)
	DECLARE @revisionNumber VARCHAR(50)
	DECLARE @partNumberCursor CURSOR
	DECLARE @isProcessStepIDSelected INT

	DECLARE @RESULTS_TABLE TABLE (  BaseProductName VARCHAR(50),
					selected INT,
				        Part_Number VARCHAR(50),
					ProcessStepID INT,
					specFileName VARCHAR(50),
					revisionNumber VARCHAR(50) )

	/*IF( @processName = 'Test' )
	BEGIN
	    SET @processName = 'FT'    
	END*/	

	SET @partNumberCursor = CURSOR for SELECT P_Parent_Product_ID, Part_Number, P.ProcessStep_ID FROM PRODUCT P, PROCESS_STEP PS WHERE P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND PS_TYPE_NAME = @processName AND P_TYPE IS NULL 
	ORDER BY P_Parent_Product_ID, PART_NUMBER
 
	OPEN @partNumberCursor 
	FETCH NEXT FROM @partNumberCursor into @baseProduct_ProductID, @part_Number, @processStepID
 
  	WHILE @@fetch_status = 0
  	BEGIN
		SELECT @baseProductName = Part_Number FROM PRODUCT WHERE Product_ID = @baseProduct_ProductID
		SELECT @specFileName = Spec_File_Name, @revisionNumber = Revision FROM Spec_Table, Product_Spec_table WHERE Spec_Table.SpecFile_Id = Product_Spec_table.SpecFile_Id AND Spec_Table.Process_Type = @processName AND Product_Spec_table.ProcessStep_Id = @processStepID
		SET @isProcessStepIDSelected = 0 --not selected 

		/*SELECT @isProcessStepIDSelected = COUNT ( * ) FROM Spec_Table, Product_Spec_table WHERE Spec_Table.SpecFile_Id = Product_Spec_table.SpecFile_Id AND Spec_Table.Process_Type = @processName AND Product_Spec_table.ProcessStep_Id = @processStepID
		IF ( @isProcessStepIDSelected = 0 )
		BEGIN
		    INSERT INTO @RESULTS_TABLE (  BaseProductName, selected, Part_Number, ProcessStepID, specFileName, revisionNumber  ) VALUES ( @baseProductName, 1, @part_Number, @processStepID, @specFileName, @revisionNumber  )
		END*/

        INSERT INTO @RESULTS_TABLE (  BaseProductName, selected, Part_Number, ProcessStepID, specFileName, revisionNumber  ) VALUES ( @baseProductName, 1, @part_Number, @processStepID, @specFileName, @revisionNumber  )

		SET @baseProductName    = null
		SET @specFileName       = null
		SET @revisionNumber     = null

		FETCH NEXT FROM @partNumberCursor into @baseProduct_ProductID, @part_Number, @processStepID
	END

  	CLOSE @partNumberCursor
  	DEALLOCATE @partNumberCursor

	SELECT * FROM @RESULTS_TABLE ORDER BY BaseProductName, Part_Number
END

